Let's take a look at three very simple functions very similar functions but very powerful functions
.
These deal with text based operations.
Take a look at the example that I have here.
I'm back inside the exercise file.
This is called the left right Mid functions worksheet and the example that I have set up here.
We've got a simple little table on the left skew number product name retail price.
And then on the right we've got a breakdown of the skew number.
The first three characters of the skew represent the supplier ID the middle three represent the part
number and the last two represent the product code.
Now if I could do this manually and I'm sure some of us have done this before you ever ever need to
break up a value some text value into separate pieces.
You know what I think about this I think about the VIN number on my car right the vehicle identification
number but it tells me different things about the car tells me about the engine the transmission and
so on all this information can be brought together into a single value.
But occasionally I want to break it up into its individual pieces so I could do things like sort on
it or filter on it or you know report off of this data perhaps Group on it within a pivot table or whatever
.
But I need to build to break this data up into its individual pieces before I can really work with it
independently.
So here insteps left right and made three very powerful functions but very simple functions that we
can use inside of Excel to help us separate these values.
Now really as you probably tell by their names their positional functions I need to find data that's
on the left hand side of a cell.
I need to find data that's on the right hand side of a cell.
I need to find data that someplace in the middle of the cell.
Well no problem.
We've got these three very similar functions to do this for us.
Take a look.
So I'll do supplier ID first.
Let me go on the E for all go up to my formulas tab.
I'm going to go into texts and there's a whole slew of text based functions and I'm going to find left
or right now where you see right here in just a moment the right function left and right are almost
identical the only difference is position whether the data you're looking for is on the left hand side
of the cell or the right hand side.
Other than that they're exactly the same.
Watch this.
So first step text once to know where the text is at that I want to look at that I want to get.
So in my case I'm inside of the number four row.
So I'm going to grab the second skew here which is that of a four.
There's my text now numb cars numb char's.
This is the number of characters that I want to get from that cell on the left hand side.
I want to get three.
And this is it super simple.
Where's the text at.
How many characters do you want on the left.
OK.
And I'm done a CM.
I say another one is new product code.
Well where product code at.
Is it on the left hand side of the cell.
I'm looking at a four here is it in the middle.
Is it on the right.
Yeah it's on the right.
So once again I'm inside of g for formulas text.
This time I'm going to find.
Right.
Look at that exact same arguments.
First step where is the text that's in a four.
How many characters do I want this time from the right hand side.
I want to.
And that's it.
WW is extracting the data out positionally.
It's on the left it's on the right.
Well how many characters do you want from the left or the right.
All right here's where it gets a little bit trickier.
Not so much it's still really simple but now we need to get something from the middle I need to get
the middle three characters from the skew.
Well here I am inside of four.
I mean to go back to formulas back detached and this time I'm going to find mid.
All right.
Now this one's a little bit different.
First that this is a look from your text while my text is inside of a four.
All right.
Now the Mid function we need to find it is someplace in the middle.
Well the Mid function starts on the left.
So at first it needs to know how many characters do you want me to move from the left so that I get
someplace in the middle.
Well for us we're going to go a C and that's three 1 2 three four.
We need to get started at this number 1 here.
So I'm in it tell it hey let's go for characters.
What do you think.
That sounds right.
Let's take a look down here at the bottom we got what Start number is.
It's the position is the position of the first character you want to extract the first character in
text is 1.
So a is 1 what we're going to tell it is the first character that we want to extract which is the fourth
character.
And then how many characters do we want.
Well I want to get the middle three and type in three.
OK.
And there it is 1 11 ACM for the player id 111 11 for part number and W.W. for product code.
Really simple.
Very very efficient functions very powerful functions.
If you've ever done this manually retyping values based on something else.
What a task.
What a headache.
Get Excel to do it for you.
So try this out.
Get comfortable working with left right and mid